﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.IO;
using System.Net;
using System.Net.Sockets;
using System.Collections;
using System.Configuration;
using System.Diagnostics;

namespace RestFulService
{
    /// <summary> 
    /// 说明：这是一个针对System.Data.SQLite的数据库常规操作封装的通用类。 
    /// </summary>
    public class SQLiteDBHelper
    {
        
        protected string connectionString;
        protected ArrayList FileList = new ArrayList();

        /// <summary> 
        /// 构造函数 
        /// </summary> 
        /// <param name="dbPath">SQLite数据库文件路径</param> 
        public SQLiteDBHelper()
        {
            
        }

        /// <summary> 
        /// 创建SQLite数据库文件 
        /// </summary> 
        /// <param name="dbPath">要创建的SQLite数据库文件路径</param> 
        public void CreateDB(string sOrgNo,string sPath, DBSERVER tmpDB)
        {
            LogFile Log = new LogFile();

            //如果不存在改数据库文件，则创建该数据库文件
            //string path = System.Environment.CurrentDirectory + "\\"+ sOrgNo;
            string path = sPath + "\\" + sOrgNo;
            if (!Directory.Exists(path))//判断是否存在
            {
                Directory.CreateDirectory(path);//创建新路径
            }

            string dbFile = path + "\\master.db";
            if (File.Exists(dbFile))
            {
                System.IO.File.Delete(dbFile);
            }

            SQLiteConnection.CreateFile(dbFile);

            connectionString = "Data Source="+ dbFile + ";Version=3;";

            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                //先建表
                FileList.Clear();
                path = System.Environment.CurrentDirectory + "\\sql" ;
                doReadFile(path);
                foreach (string sfilename in FileList)
                {
                    // 读取文本文件
                    string line = ReadTxtFile(sfilename);
                    SQLiteCommand cmd = new SQLiteCommand();
                    cmd.Connection = connection;
                    cmd.CommandText = line;
                    cmd.ExecuteNonQuery();
                }

                //导入数据
                FileList.Clear();
                path = System.Environment.CurrentDirectory + "\\txt";
                doReadFile(path);
                foreach (string sfilename in FileList)
                {
                    // 读取文本文件
                    string tablename = System.IO.Path.GetFileNameWithoutExtension(sfilename);// 没有扩展名的文件名 “Default”
                    string line = ReadTxtFile(sfilename);

                    line = line.Replace("{WarehouseID}", "'" + sOrgNo + "'");

                    DataTable dtData = tmpDB.query(line);
                    for (int m = 0; m < dtData.Rows.Count; m++)
                    {
                        string sValue = "";
                        for(int j = 0; j < dtData.Columns.Count; j++)
                        {
                            sValue = sValue + ",'"+ dtData.Rows[m][j].ToString()+"'" ;
                        }
                        sValue = sValue.Substring(1, sValue.Length - 1);
                        sValue = "insert into " + tablename + " values(" + sValue + ");";

                        try
                        {
                            SQLiteCommand cmd = new SQLiteCommand();
                            cmd.Connection = connection;
                            cmd.CommandText = sValue;
                            cmd.ExecuteNonQuery();
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine("写入错误:" + ex.Message);
                            Log.doWriteLog(1, "写入错误:" + ex.Message);
                            Log.doWriteLog(1, sValue);
                        }
                    }

                    
                }
                //connection.Shutdown();
                connection.Clone();
                connection.Close();

                SQLiteConnection.ClearAllPools();


            }
        }

        //读取目录下所有文件
        public void doReadFile(string file)
        {
            try
            {
                //判断文件夹是否还存在
                if (Directory.Exists(file))
                {
                    foreach (string f in Directory.GetFileSystemEntries(file))
                    {
                        if (File.Exists(f))
                        {
                            //如果有子文件删除文件
                            FileList.Add(f);
                            //Console.WriteLine(f);
                        }
                        else
                        {
                            //循环递归删除子文件夹
                            doReadFile(f);
                        }
                    }
                }

            }
            catch (Exception ex) // 异常处理
            {
                Console.WriteLine(ex.Message.ToString());// 异常信息
            }
        }

        //读取超大文本文件
        //https://blog.csdn.net/txwtech/article/details/122449888
        private string ReadTxtFile(string fileName)
        {

            string txt = "";
            // var fileDir = this.txtFileFolder.Text.Trim();
            var fileDir = fileName;

            byte[] allBytes = null;

            byte[] buffer = new byte[1024];//一个1K的缓冲字节容器
            Stopwatch stopwatch = new Stopwatch();
            stopwatch.Restart();
            using (MemoryStream ms = new MemoryStream())
            {
                using (FileStream fs = new FileStream(fileDir, FileMode.Open, FileAccess.Read))
                {
                    int positon = 0;
                    while ((positon = fs.Read(buffer, 0, buffer.Length)) > 0)
                    {
                        ms.Write(buffer, 0, positon);

                    }

                    allBytes = ms.ToArray();
                }

            }
            stopwatch.Stop();
            // MessageBox.Show($"StreamReader StreamWriter 程序运行花费的时间:{(double)stopwatch.ElapsedMilliseconds / 1000:0.000} secs");

            if (null != allBytes)
            {
                //尝试将字节转成字符串
                //txt = System.Text.Encoding.UTF8.GetString(allBytes);

                txt = System.Text.Encoding.Default.GetString(allBytes);

            }
            string[] txtToArray = txt.Split('\r');
            //  ReadData_List.Add(txtToArray);
            return txt;

        }

        /// <summary> 
        /// 对SQLite数据库执行增删改操作，返回受影响的行数。 
        /// </summary> 
        /// <param name="sql">要执行的增删改的SQL语句</param> 
        /// <param name="parameters">执行增删改语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param> 
        /// <returns></returns> 
        //public void doSave(out bool IsOk, out string sError)
        //{
        //    IsOk = true;
        //    sError = "";
        //    using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        //    {
        //        connection.Open();
        //        using (DbTransaction transaction = connection.BeginTransaction())
        //        {
        //            using (SQLiteCommand command = new SQLiteCommand(connection))
        //            {
        //                //for (int i = 0; i < SQLList.Length; i++)
        //                foreach (string SQL in Global.SQLList)
        //                {
        //                    try
        //                    {
        //                        command.CommandText = SQL;
        //                        command.ExecuteNonQuery();
        //                    }
        //                    catch (Exception e)
        //                    {
        //                        sError = e.Message;
        //                        Console.WriteLine(e.Message);
        //                        LogFile Log = new LogFile();
        //                        Log.doWriteLog(1, e.Message + "\n" + SQL);
        //                        IsOk = false;
        //                        break;
        //                    }
        //                }
        //            }
        //            if (IsOk)
        //                transaction.Commit();
        //            else
        //                transaction.Rollback();

        //            connection.Close();

        //        }
        //    }
        //}

        /// <summary> 
        /// 执行一个查询语句，返回一个包含查询结果的DataTable 
        /// </summary> 
        /// <param name="sql">要执行的查询语句</param> 
        /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param> 
        /// <returns></returns> 
        //public DataTable query(string sql)
        //{
        //    using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        //    {
        //        using (SQLiteCommand command = new SQLiteCommand(sql, connection))
        //        {
        //            SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
        //            DataTable data = new DataTable();
        //            adapter.Fill(data);
        //            connection.Close();
        //            return data;
        //        }

        //    }
        //}


    }


}
